﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class OrderDAO
    {
        public const int ITEM_STATE_WAITING = 0;
        public const int ITEM_STATE_PROCESSING = 1;
        public const int ITEM_STATE_DONE = 2;

        public const int ORDER_STATE_ORDERING = TableDAO.STATE_ORDERING;  // 未提交
        public const int ORDER_STATE_ORDERED = TableDAO.STATE_ORDERED;  // 已提交
        public const int ORDER_STATE_PAID = TableDAO.STATE_PAID;  // 已结帐
        public const int ORDER_STATE_HISTORY = 9;  // 历史订单

        public string addNewOrder(string strOperName, string tableName, string waiterID, double payed)
        {
            string orderID = createOrderID();
            SqlConnection con = null;
            try
            {
                // create and open a connection
                con = ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_addNewOrder", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] sparams = {
                                             new SqlParameter("@orderID", SqlDbType.VarChar, 20),
                                             new SqlParameter("@tableName", SqlDbType.VarChar, 20),
                                             new SqlParameter("@waiterID", SqlDbType.VarChar, 10),
                                             new SqlParameter("@payed", SqlDbType.Decimal),
                                             new SqlParameter("@orderState", SqlDbType.Int),
                                             new SqlParameter("@orderTime", SqlDbType.DateTime)
                                         };
                sparams[0].Value = orderID;
                sparams[1].Value = tableName;
                sparams[2].Value = waiterID;
                sparams[3].Value = payed;
                sparams[4].Value = TableDAO.STATE_ORDERING;
                sparams[5].Value = DateTime.Now;
                // Add parameters
                foreach (SqlParameter param in sparams)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();
                new TableDAO().setTableState(tableName, TableDAO.STATE_ORDERING);

                string strDetails = "添加桌位: "+tableName+", 订单: "+orderID+"成功";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = "添加桌位: " + tableName+"失败";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null) { con.Close(); con.Dispose(); con = null; };
            }

            return orderID;
        }

        // Create a 20 length orderID string with time string and three random ABC or digital.
        private string createOrderID()
        {
            StringBuilder strbf;
            string radStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
            int len = radStr.Length;
            Random rand = new Random();
            do
            {
                strbf = null;
                strbf = new StringBuilder();
                strbf.Append(DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                for (int i = 0; i < 3; i++)
                {
                    int randNum = rand.Next(len);
                    strbf.Append(radStr.Substring(randNum, 1));
                }                
            } while (isOrderExist(strbf.ToString()));
            
            return strbf.ToString();
        }

        /**
         * Load order items from db by strOrderID and set into dgv compenent.
         * @param {string} strOrderID The string value of order id.
         * @param {DataGridView} dgv The compenent that will be setted.
         */
        public DataSet loadOrderItems(string strOrderID)
        {
            DataSet ds = new DataSet();
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                SqlDataAdapter sda = new SqlDataAdapter("pr_getOneOrder", con);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter[] prams = {
                                           new SqlParameter("@orderID", SqlDbType.VarChar, 20)
                                       };
                prams[0].Value = strOrderID;
                sda.SelectCommand.Parameters.Add(prams[0]);
                sda.Fill(ds);
                return ds;
            }
            catch (Exception e) { throw e; }
            finally
            {
                if (con != null)  { con.Close(); con.Dispose(); con = null; }
            }
        }

        /**
         * Get order mount money.
         */
        public double getOrderTotalPrice(string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_getOrderTotalPrice", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] prams = {
                                           new SqlParameter("@orderID", SqlDbType.VarChar, 20)
                                       };
                prams[0].Value = strOrderID;
                cmd.Parameters.Add(prams[0]);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return double.Parse(sdr[0].ToString());
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
            return 0.0d;
        }

        public void addOrderFood(string strOperName, string strOrderID, string strFoodID, int iCount, string strNotes, string strCookID)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_addOrderItem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] sparams = {
                                                     new SqlParameter("@orderID", SqlDbType.VarChar, 20),
                                                     new SqlParameter("@foodID", SqlDbType.VarChar, 8),
                                                     new SqlParameter("@mount", SqlDbType.Int),
                                                     new SqlParameter("@notes", SqlDbType.VarChar, 20),
                                                     new SqlParameter("@cookID", SqlDbType.VarChar, 10)
                                                 };
                sparams[0].Value = strOrderID;     // orderID
                sparams[1].Value = strFoodID;   // foodID
                sparams[2].Value = iCount; // count
                sparams[3].Value = strNotes; // notes
                sparams[4].Value = strCookID;  // cookID

                // Add parameters
                foreach (SqlParameter param in sparams)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();

                string strDetails = "添加订单号: "+strOrderID+", 编号: " + strFoodID + "成功";
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e1)
            {
                string strDetails = string.Format("添加订单号: {0}, 编号: {1} 失败。详细日志：{2}",
                                                                    strOrderID, strFoodID, e1.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /**
         * Update food in order list.
         * @return True for update success, False for this food isn't exist or update failure.
         */
        public bool updateOrderFood(string strOperName, string strOrderID, string strFoodID, int iCount, string strNotes, string strCookID)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_updateOrderItem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] sparams = {
                                                     new SqlParameter("@orderID", SqlDbType.VarChar, 20),
                                                     new SqlParameter("@foodID", SqlDbType.VarChar, 8),
                                                     new SqlParameter("@mount", SqlDbType.Int),
                                                     new SqlParameter("@notes", SqlDbType.VarChar, 20),
                                                     new SqlParameter("@cookID", SqlDbType.VarChar, 10)
                                                 };
                sparams[0].Value = strOrderID;     // orderID
                sparams[1].Value = strFoodID;   // foodID
                sparams[2].Value = iCount; // count
                sparams[3].Value = strNotes; // notes
                sparams[4].Value = strCookID;  // cookID

                // Add parameters
                foreach (SqlParameter param in sparams)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();

                string strDetails = string.Format("更新订单号: {0}, 编号: {1} 成功", strOrderID, strFoodID);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e1)
            {
                string strDetails = string.Format("更新订单号: {0}, 编号: {1} 失败。详细日志：{2}",
                                                                    strOrderID, strFoodID, e1.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
            return true;
        }

        /**
         * Remove food item from one order.
         * @param {string} strOrderID The order id that will remove from
         * @param {string} strFoodID The food id which will be removed
         */
        public bool removeOrderFood(string strOperName, string strOrderID, string strFoodID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                SqlCommand cmd = new SqlCommand("pr_removeOrderItem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] sparams = {
                                                 new SqlParameter("@orderID", SqlDbType.VarChar, 20),
                                                 new SqlParameter("@foodID", SqlDbType.VarChar, 8)
                                             };
                sparams[0].Value = strOrderID;     // orderID
                sparams[1].Value = strFoodID;   // foodID

                // Add parameters
                foreach (SqlParameter param in sparams)
                {
                    cmd.Parameters.Add(param);
                }
                cmd.ExecuteNonQuery();

                string strDetails = string.Format("删除订单号: {0}, 编号: {1} 成功", strOrderID, strFoodID);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = string.Format("删除订单号: {0}, 编号: {1} 失败。详细日志：{2}",
                                                                    strOrderID, strFoodID, e.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
            return true;
        }

        /**
         * Submit one order.
         * @return {int} 0: Normal, 1: Order have be submitted
         */
        public int submitOrder(string strOperName, string strOrderID, string strTableName)
        {
            try
            {
                if (this.isOrderSubmit(strOrderID))
                    return 1;
                this.setOrderState(strOrderID, ORDER_STATE_ORDERED);
                new TableDAO().setTableState(strTableName, TableDAO.STATE_ORDERED);
                string strDetails = string.Format("提交 订单号: {0} 成功", strOrderID);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
                return 0;
            }
            catch (Exception e1)
            {
                string strDetails = string.Format("提交 订单号: {0} 失败。详细日志：{1}", strOrderID, e1.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                return 2;
            }
        }

        public bool isOrderSubmit(string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format(
                                            "SELECT COUNT(orderID) FROM tb_orderlist WHERE orderID='{0}' "
                                            +"AND orderState={1} GROUP BY orderID;"
                                            , strOrderID,
                                            ORDER_STATE_ORDERED);
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) > 0)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
            return false;
        }

        // Get un-payed order information For payed.
        public DataSet getPayingOrderInfo(string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format(
                                            "SELECT tb_orderlist.tableName, orderTime, waiterID,"
                                            +"tablePrice, payed, totalPrice, num, notes "
                                            +"FROM tb_table, tb_orderlist "
                                            +"WHERE tb_table.tableName=tb_orderlist.tableName "
                                            +"AND orderState={1} "
                                            +"AND orderID='{0}';"
                                            , strOrderID,
                                            ORDER_STATE_ORDERED);
                Console.WriteLine(strSql);
                SqlDataAdapter sda = new SqlDataAdapter(strSql, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return ds;
            }
            catch (Exception e) { throw e; }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /*Remove order*/
        public void removeOrder(string strOperName, string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("DELETE FROM tb_orderlist WHERE orderID='{0}';" , strOrderID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                string strDetails = string.Format("删除 订单号: {0} 成功", strOrderID);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = string.Format("删除 订单号: {0} 失败。详细日志：{1}", strOrderID, e.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /*
         * User payed total money of this order then close order.
         */
        public void closeOrder(string strOperName, string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format(
                                            "UPDATE  tb_table SET tableState={2} WHERE tableName="+
                                            "(SELECT tableName FROM tb_orderlist WHERE orderID='{0}' AND orderState={1});",
                                            strOrderID,
                                            ORDER_STATE_ORDERED,
                                            ORDER_STATE_PAID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                this.setOrderState(strOrderID, ORDER_STATE_PAID);

                string strDetails = string.Format("订单号: {0} 结帐成功", strOrderID);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = string.Format("订单号: {0} 结帐失败。详细日志：{1}", strOrderID, e.Message);
                RMS.Logger.Add(strOperName, strDetails, RMS.Logger.ERROR);
                throw e; 
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public void setOrderState(string strOderID, int iOrderState)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("update tb_orderlist set orderState={0} where orderID='{1}';",
                                                        iOrderState, strOderID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } };
        }

        public string getOrderIDByTableName(string strTableName)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format(
                                            "SELECT orderID FROM tb_table, tb_orderlist "
                                            +" WHERE tb_table.tableName=tb_orderlist.tableName "
                                            +"AND orderState in ({1}, {2}, {3}) "
                                            +"AND tb_tableName='{0}' ",
                                            strTableName,
                                            ORDER_STATE_ORDERED,
                                            ORDER_STATE_ORDERING,
                                            ORDER_STATE_PAID
                                        );
                SqlCommand cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return sdr[0].ToString().Trim();
                }
                return null;
            }
            catch (Exception e) { throw e; }
            finally { if (con != null) { con.Close(); con.Dispose(); con = null; } }
        }

        public bool isOrderExist(string strOrderID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = String.Format(
                                            "select COUNT(orderID)  from tb_orderlist where orderID='{0}';",
                                            strOrderID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return (Int32.Parse(sdr[0].ToString()) == 1);
                }
                return false;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        // 判断一道菜是否可以取消与及修改
        public bool isOrderFoodOperAvail(string strOrderID, string strFoodID)
        {
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("select COUNT(foodID) from tb_menu where foodID='{0}' and foodState=1;", strFoodID);
                SqlCommand command = new SqlCommand(strSql, con);

                // call ExecuteReader() to get a data reader
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) == 1)
                    {
                        sdr.Close();
                        return true;
                    }
                }
                sdr.Close();
                return false;
            }
            catch (Exception e1)
            {
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }
    }
}
